package com.example.spring.mapper;


import com.example.spring.Dto.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ProductDetailsMapper {

    @Select("SELECT province, COUNT(*) AS totalOrderCount " +
            "FROM crm_db.sales_records " +
            "WHERE province IS NOT NULL " +
            "GROUP BY province")
    List<ProvinceOrderCountDTO> countOrdersByProvince();

    // 获取每个省份前十的客户数量
    @Select("SELECT " +
            "  province, " +
            "  customer_name AS customerName, " +
            "  orderCount, " +
            "  customer_rank " +
            "FROM (" +
            "  SELECT " +
            "    province, " +
            "    customer_name, " +
            "    COUNT(*) AS orderCount, " +
            "    ROW_NUMBER() OVER (PARTITION BY province ORDER BY COUNT(*) DESC) AS customer_rank " +
            "  FROM crm_db.sales_records " +
            "  WHERE province IS NOT NULL AND customer_name IS NOT NULL " +
            "  GROUP BY province, customer_name " +
            ") AS ranked " +
            "WHERE customer_rank <= 10 " +
            "ORDER BY province, customer_rank")
    @Results({
            @Result(property = "province", column = "province"),
            @Result(property = "customerName", column = "customerName"),
            @Result(property = "orderCount", column = "orderCount"),
            @Result(property = "rank", column = "customer_rank")
    })
    List<ProvinceTopCustomerDTO> getTopCustomersByProvince();

    // 统计每个省份的客户数量
    @Select("SELECT " +
            "  province, " +
            "  COUNT(DISTINCT customer_name) AS customerCount " +
            "FROM crm_db.sales_records " +
            "WHERE province IS NOT NULL AND customer_name IS NOT NULL " +
            "GROUP BY province " +
            "ORDER BY customerCount DESC")
    @Results({
            @Result(property = "province", column = "province"),
            @Result(property = "customerCount", column = "customerCount")
    })
    List<ProvinceCustomerCountDTO> countCustomersByProvince();

    // 获取客户数量前10的省份
    @Select("SELECT " +
            "  province, " +
            "  COUNT(DISTINCT 客户姓名) AS customerCount " +
            "FROM (" +
            "  SELECT province, 客户姓名 FROM product_details_2021 WHERE province IS NOT NULL AND 客户姓名 IS NOT NULL " +
            "  UNION ALL " +
            "  SELECT province, 客户姓名 FROM product_details_2022 WHERE province IS NOT NULL AND 客户姓名 IS NOT NULL " +
            "  UNION ALL " +
            "  SELECT province, 客户姓名 FROM product_details_2023 WHERE province IS NOT NULL AND 客户姓名 IS NOT NULL " +
            "  UNION ALL " +
            "  SELECT province, 客户姓名 FROM product_details_2024 WHERE province IS NOT NULL AND 客户姓名 IS NOT NULL " +
            "  UNION ALL " +
            "  SELECT province, 客户姓名 FROM product_details_2025 WHERE province IS NOT NULL AND 客户姓名 IS NOT NULL " +
            ") AS combinedData " +
            "GROUP BY province " +
            "ORDER BY customerCount DESC " +
            "LIMIT 10")
    @Results({
            @Result(property = "province", column = "province"),
            @Result(property = "customerCount", column = "customerCount")
    })
    List<ProvinceCustomerCountDTO> getTop10ProvincesByCustomerCount();

    // 统计每个省份的记录数量（从sales_records表）
    @Select("SELECT " +
            "  province, " +
            "  COUNT(*) AS customerCount " +
            "FROM crm_db.sales_records " +
            "WHERE province IS NOT NULL " +
            "GROUP BY province " +
            "ORDER BY customerCount DESC")
    @Results({
            @Result(property = "province", column = "province"),
            @Result(property = "customerCount", column = "customerCount")
    })
    List<ProvinceCustomerCountDTO> countCustomersByProvinceFromSalesRecords();

    // 获取记录数量前10的省份（从sales_records表）
    @Select("SELECT " +
            "  province, " +
            "  COUNT(*) AS customerCount " +
            "FROM crm_db.sales_records " +
            "WHERE province IS NOT NULL " +
            "GROUP BY province " +
            "ORDER BY customerCount DESC " +
            "LIMIT 10")
    @Results({
            @Result(property = "province", column = "province"),
            @Result(property = "customerCount", column = "customerCount")
    })
    List<ProvinceCustomerCountDTO> getTop10ProvincesByCustomerCountFromSalesRecords();

    @Select("SELECT '2021' AS year, COUNT(*) AS order_count FROM crm_db.sales_records " +
            "WHERE purchase_date LIKE '2021%' " +
            "UNION ALL " +
            "SELECT '2022' AS year, COUNT(*) AS order_count FROM crm_db.sales_records " +
            "WHERE purchase_date LIKE '2022%' " +
            "UNION ALL " +
            "SELECT '2023' AS year, COUNT(*) AS order_count FROM crm_db.sales_records " +
            "WHERE purchase_date LIKE '2023%' " +
            "UNION ALL " +
            "SELECT '2024' AS year, COUNT(*) AS order_count FROM crm_db.sales_records " +
            "WHERE purchase_date LIKE '2024%' " +
            "UNION ALL " +
            "SELECT '2025' AS year, COUNT(*) AS order_count FROM crm_db.sales_records " +
            "WHERE purchase_date LIKE '2025%'")
    List<YearlyOrderCountDTO> countOrdersByYear();
    //数据总量
    @Select("SELECT COUNT(*) AS totalOrderCount FROM crm_db.sales_records")
    @Results({
            @Result(property = "totalOrderCount", column = "totalOrderCount")
    })
    OrderCountDTO getTotalOrderCount();


    //华东 华北 中南 西南 西北各个区域21-25年的客户订单数量占比
    @Select("SELECT region, " +
            "ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM crm_db.sales_records WHERE region IS NOT NULL), 2) AS orderPercentage " +
            "FROM crm_db.sales_records " +
            "WHERE region IS NOT NULL " +
            "GROUP BY region")
    @Results({
            @Result(property = "region", column = "region"),
            @Result(property = "orderPercentage", column = "orderPercentage")
    })
    List<RegionOrderCountDTO> getRegionOrderCounts();



    @Select("SELECT region, COUNT(*) AS totalOrderCount " +
            "FROM crm_db.sales_records " +
            "WHERE region IS NOT NULL " +
            "GROUP BY region")
    @Results({
            @Result(property = "region", column = "region"),
            @Result(property = "totalOrderCount", column = "totalOrderCount")
    })
    List<RegionOrderCount> getRegionOrderCount();



}